package com.coderxq.blog.utils.excel;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
/**
 * @Author: lijingming
 * @Date: 2024/12/13
 * @Description:
 **/
public class ExcelUtil {
    private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    public static Sheet exportSheet=null; //导出excel文件的sheet对象实例
    public static Workbook exportWorkbook=new XSSFWorkbook(); //导出excel文件的workbook实例
    public static void setExportWorkbook(String templatePath) throws IOException {
        FileInputStream fis = new FileInputStream(templatePath);
        exportWorkbook= new XSSFWorkbook(fis);
        exportSheet = exportWorkbook.getSheetAt(0); // 使用模板中的第一个sheet
    }
    public static Sheet getExportSheet(){
        if(ExcelUtil.exportSheet==null){
            ExcelUtil.exportSheet = exportWorkbook.createSheet("合并单元格案例");
        }
        return ExcelUtil.exportSheet;
    }



    /***
     * 导出excel并对导出的数据进行合并单元格
     * @param tableData
     * @param headerData
     * @return
     */
    public static   String dataToExcelUnitTable(List<List<String>> tableData,List<List<String>> headerData) {
        ByteArrayOutputStream bas = null;
        final String savePath = "D://upload//Excel导出合并单元格_"+new Date().getTime()+".xlsx";
        try {
            // 创建工作表
              Sheet  exportSheet = getExportSheet();

            //表头已经占用的行数
            final int tbHeadUseNo = headerData.size();
            // 设置表数据样式
            final CellStyle tbStyle = createDataStyle();
            List<ExcelMergedRegion> mergedRegionLists = new ArrayList<>(); // 需要合并的单元格

            for (int rowIndex = 0; rowIndex < tableData.size(); rowIndex++) {
                //创建每一行

                final Row row = exportSheet.createRow(rowIndex+tbHeadUseNo);
                final List<String> rowData = tableData.get(rowIndex);
                final int StartMergeRowIndex =tbHeadUseNo; //开始合并的内容行区域
                for (int columnIndex = 0; columnIndex < rowData.size(); columnIndex++) {
                    if(columnIndex==0&&rowIndex==1){
                        //log.info(rowData.toString());
                    }
                    //判断同列上下行合并
                    List<String> lastRowData =rowIndex==0?null:tableData.get(rowIndex-1); //上一行数据
                    ExcelMergedRegion mergedRegion=null;
                    int curMergeRowIndex = rowIndex;//当前开始合并的行序号
                    Boolean isNextRowMerge = true; //是否进入循环判断下一行能否进行合并
                    List<String> nextRowData = curMergeRowIndex==tableData.size()-1?null:tableData.get(curMergeRowIndex+1);
                    if(lastRowData != null && !lastRowData.get(columnIndex).equals(rowData.get(columnIndex))){
                        mergedRegion=new ExcelMergedRegion();
                        mergedRegion.setFirstCol(columnIndex);
                        mergedRegion.setFirstRow(rowIndex);

                        while (nextRowData!=null&&isNextRowMerge){
                            if(nextRowData.get(columnIndex).equals(rowData.get(columnIndex))){
                                curMergeRowIndex+=1;
                            }else{
                                isNextRowMerge = false;
                            }
                            nextRowData = curMergeRowIndex==tableData.size()-1?null:tableData.get(curMergeRowIndex+1);
                        }

                    }else if(lastRowData == null){
                        mergedRegion=new ExcelMergedRegion();
                        mergedRegion.setFirstCol(columnIndex);
                        mergedRegion.setFirstRow(rowIndex);
                        while (nextRowData!=null&&isNextRowMerge){
                            if(nextRowData.get(columnIndex).equals(rowData.get(columnIndex))){
                                curMergeRowIndex+=1;
                            }else{
                                isNextRowMerge = false;
                            }
                            nextRowData =curMergeRowIndex==tableData.size()-1?null:tableData.get(curMergeRowIndex+1);
                        }
                    }

                    //判断是否进行列合并
                    String lastColData =columnIndex==0?null: rowData.get(columnIndex-1);
                    int curMergeColIndex = columnIndex;
                    Boolean isNextColMerge = true;
                    String nextColData =curMergeColIndex==rowData.size()-1?null: rowData.get(curMergeColIndex+1);

                    if(lastColData!=null&&!lastColData.equals(rowData.get(columnIndex))){
                        mergedRegion=mergedRegion==null?new ExcelMergedRegion():mergedRegion;
                        mergedRegion.setFirstRow(rowIndex);
                        mergedRegion.setFirstCol(columnIndex);

                            while (nextColData != null && isNextColMerge) {
                                if (nextColData.equals(rowData.get(columnIndex))) {
                                    curMergeColIndex += 1;
                                } else {
                                    isNextColMerge = false;
                                }
                                nextColData = curMergeColIndex == rowData.size() - 1 ? null : rowData.get(curMergeColIndex+1);
                            }

                    }else if(lastColData == null){
                        mergedRegion=mergedRegion==null?new ExcelMergedRegion():mergedRegion;
                        mergedRegion.setFirstRow(rowIndex);
                        mergedRegion.setFirstCol(columnIndex);

                        while (nextColData!=null&&isNextColMerge){
                            if(nextColData.equals(rowData.get(columnIndex))){
                                curMergeColIndex+=1;
                            }else{
                                isNextColMerge = false;
                            }
                            nextColData = curMergeColIndex == rowData.size() - 1 ? null : rowData.get(curMergeColIndex+1);

                        }

                    }

                    //判断合并对象是否存在
                    if(mergedRegion!=null&&(curMergeColIndex!=mergedRegion.getFirstCol()||curMergeRowIndex!=mergedRegion.getFirstRow())){
                        mergedRegion.setFirstCol(columnIndex);
                        mergedRegion.setLastCol(curMergeColIndex);
                        mergedRegion.setFirstRow(StartMergeRowIndex+rowIndex);
                        mergedRegion.setLastRow(curMergeRowIndex+StartMergeRowIndex);

                        mergedRegionLists.add(mergedRegion);
                    }


                    Cell cell = row.createCell(columnIndex);
                    //cell.setCellType(CellType.STRING);
                    cell.setCellValue(rowData.get(columnIndex).toString());
                    cell.setCellStyle(tbStyle);
                }
            }

            // 合并数据单元格 垂直合并
            log.info("合并单元格列表" + mergedRegionLists);
            for (ExcelMergedRegion mergedRegion : mergedRegionLists){
                log.info("合并单元格" + mergedRegion);

                //添加合并单元格区域实例对象 ，该对象会对所包含的单元格区域进行合并
                exportSheet.addMergedRegion(new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstCol(), mergedRegion.getLastCol()));
            }

            //exportSheet.addMergedRegion(new CellRangeAddress(2, 5, 0, 0));//性别男合并
            //exportSheet.addMergedRegion(new CellRangeAddress(6,10, 0, 0));//性别女合并
            //exportSheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));//班级合并
            //exportSheet.addMergedRegion(new CellRangeAddress(4, 5, 1, 1));//班级合并
            //exportSheet.addMergedRegion(new CellRangeAddress(6, 7, 1, 1));//班级合并
            //exportSheet.addMergedRegion(new CellRangeAddress(8,10, 1, 1));//班级合并
            //
            bas = new ByteArrayOutputStream();
            exportWorkbook.write(bas);

            FileUtils.writeByteArrayToFile(new File(savePath), bas.toByteArray());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return savePath;
    }

    /**
     * 创建表头样式.
     */
    public static CellStyle createHeadStyle() {
        final CellStyle headerCellStyle = exportWorkbook.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        headerCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        headerCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        headerCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        headerCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        headerCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);//蓝色背景色
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//全填充模式
        final Font font = exportWorkbook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());//设置字体颜色
        font.setBold(true);
        headerCellStyle.setFont(font);//表头字体加粗
        return headerCellStyle;
    }
    /**
     * 创建数据样式.
     */
    public static CellStyle createDataStyle() {
        final CellStyle tbStyle = exportWorkbook.createCellStyle();
        tbStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        tbStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        tbStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        tbStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        tbStyle.setBorderTop(BorderStyle.THIN);// 上边框
        tbStyle.setBorderRight(BorderStyle.THIN);// 右边框
        final Font tbfont = exportWorkbook.createFont();
        tbfont.setColor(IndexedColors.BLUE.getIndex());//设置字体颜色
        tbfont.setBold(false);
        tbStyle.setFont(tbfont);//表头字体加粗
        return tbStyle;
    }


    /**
     * 读取Excel文件内容
     * @param excelFile
     * @return
     * @throws IOException
     */
    public static List<List<Object>> readImportExcel(File excelFile)  throws IOException {
        List<List<Object>> exceListData= new ArrayList<>();
        FileInputStream fis = new FileInputStream(excelFile);
        Workbook readworkbook = new XSSFWorkbook(fis);
        Sheet readSheet = readworkbook.getSheetAt(0);//读取第一张sheet表
        Iterator<Row> rowIterator = readSheet.iterator();
        // 跳过表头行
        //if (rowIterator.hasNext()) {
        //    rowIterator.next();
        //}

        while (rowIterator.hasNext()) {
            List<Object> excelRowListData= new ArrayList<>();
            Row row = rowIterator.next();
            // 假设 Excel 文件的列顺序为
            int numberOfCells = getNumberOfCellsInRow(row);
            for(int i = 0; i < numberOfCells; i++){
                excelRowListData.add(getCellValue(row.getCell(i)));
            }

            exceListData.add(excelRowListData);
        }

        //readworkbook.close();
        fis.close();

        return exceListData;
    }

    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            default:
                return "";
        }
    }

    public static double getNumericCellValue(Cell cell) {
        if (cell == null || cell.getCellType() != CellType.NUMERIC) {
            return 0;
        }
        return cell.getNumericCellValue();
    }

    /**
     * 获取行中单元格的数量
     * @param row
     * @return
     */
    public static int getNumberOfCellsInRow(Row row) {
        if (row != null) {
            return row.getLastCellNum();
        } else {
            return 0; // 或者抛出异常，根据你的需求决定
        }
    }

}
